﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public DataSet QueryGLLG(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            // GSCMANDANT GSCART GSCVAR GSCFARBE


            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM GLLG WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT DISTINCT GP.* FROM ZUTEI RIGHT JOIN GPDAT GP ON ZUMANDANT=GPMANDANT AND ZUPARTIE=GPPARTIE"
                    + " WHERE  ZUMANDANT=:ZUMANDANT AND ZUAUFKRS=:ZUAUFKRS AND ZUAUFLFD=:ZUAUFLFD"; ;
                    or1.Parameters.Add("ZUMANDANT", OracleType.NVarChar).Value = dwData["ZUMANDANT"].ToString();
                    or1.Parameters.Add("ZUAUFKRS", OracleType.NVarChar).Value = dwData["ZUAUFKRS"].ToString();
                    or1.Parameters.Add("ZUAUFLFD", OracleType.NVarChar).Value = dwData["ZUAUFLFD"].ToString();
                    break;
                  
                case 2:
                           
                    or1.CommandText = "SELECT LPLBKZ,GPSPPART,GPFBPART,GPBPARTIE,GPBBSTKGZ,GPBPARTIEKZ,X.GLBEM,X.GLLBEHI,X.GPGART,X.GLLO,X.GLLB,X.GLLP,X.GPBLO,X.GPBLB,X.GPGVAR,X.GPGFARBE,X.KG,NVL(X.ALLOKG,0) AS ALLOKG,X.KG-NVL(X.ALLOKG,0) AS FREEKG FROM (SELECT GPSPPART,GPFBPART, GPBPARTIE,GPBPARTIEKZ,GPBBSTKGZ ,A.GLLBEHI,A.GLLO,A.GLLB,A.GLBEM,A.GLLP,GPBLO,GPBLB,B.GPGART,B.GPGVAR,B.GPGFARBE,A.GLBSTNET*0.001 AS KG, (SELECT SUM(DECODE(Z.ZUERLKZ,'J',0,Z.ZUEINTKG-Z.ZUENTNKG)*0.001) FROM ZUTEI Z WHERE Z.ZUMANDANT = A.GLMANDANT AND ZULO=GLLO AND ZULB=GLLB AND Z.ZUPARTIE = A.GLPARTIE AND Z.ZUPARTIEKZ = A.GLPARTIEKZ AND Z.ZULBEHI = A.GLLBEHI) AS ALLOKG,LPLBKZ  FROM GLLG A,GPDAT B,LAGPL,GPBST WHERE  A.GLLO=LPLO AND A.GLLB=LPLB AND A.GLLP=LPPLATZ AND A.GLMANDANT = B.GPMANDANT AND A.GLPARTIE = B.GPPARTIE AND GPBMANDANT=GPMANDANT AND GPBPARTIE=GPPARTIE AND GPBPARTIEKZ=GLPARTIEKZ  AND A.GLPARTIEKZ = B.GPPARTIEKZ  AND GLLO=GPBLO AND   GLLB=GPBLB"
                        + " AND GPGART=:GAART AND GPGVAR=:GAVAR AND GPGFARBE= :GAFARBE AND GPSPPART=:GPSPPART AND GPFBPART=:GPFBPART AND GLLO=:GLLO AND GLLB=:GLLB AND GLAUFMSL=:GLAUFMSL AND RTRIM(GLLO||GLLB||GLLP) NOT LIKE '" + dwData["ADDT"].ToString() + "%') X WHERE X.KG-NVL(X.ALLOKG,0)>0 "
                        +" ORDER BY KG,FREEKG,ALLOKG";                     
                    or1.Parameters.Add("GAART", OracleType.NVarChar).Value = dwData["GAART"].ToString();
                    or1.Parameters.Add("GAVAR", OracleType.NVarChar).Value = dwData["GAVAR"].ToString();
                    or1.Parameters.Add("GAFARBE", OracleType.NVarChar).Value = dwData["GAFARBE"].ToString();
                    or1.Parameters.Add("GPSPPART", OracleType.NVarChar).Value = dwData["GPSPPART"].ToString();
                    or1.Parameters.Add("GPFBPART", OracleType.NVarChar).Value = dwData["GPFBPART"].ToString();
                    or1.Parameters.Add("GLAUFMSL", OracleType.NVarChar).Value = dwData["GLAUFMSL"].ToString();
                    or1.Parameters.Add("GLLO", OracleType.NVarChar).Value = dwData["GLLO"].ToString();
                    or1.Parameters.Add("GLLB", OracleType.NVarChar).Value = dwData["GLLB"].ToString(); 
                    break;
                case 3:
            
                    or1.CommandText = "SELECT LPLBKZ,GPSPPART,GPFBPART,GPBPARTIE,GPBBSTKGZ,GPBPARTIEKZ,X.GLBEM,X.GLLBEHI,X.GPGART,X.GLLO,X.GLLB,X.GLLP,X.GPBLO,X.GPBLB,X.GPGVAR,X.GPGFARBE,X.KG,NVL(X.ALLOKG,0) AS ALLOKG,X.KG-NVL(X.ALLOKG,0) AS FREEKG FROM (SELECT GPSPPART,GPFBPART, GPBPARTIE,GPBPARTIEKZ,GPBBSTKGZ ,A.GLLBEHI,A.GLLO,A.GLLB,A.GLBEM,A.GLLP,GPBLO,GPBLB,B.GPGART,B.GPGVAR,B.GPGFARBE,A.GLBSTNET*0.001 AS KG, (SELECT SUM(DECODE(Z.ZUERLKZ,'J',0,Z.ZUEINTKG-Z.ZUENTNKG)*0.001) FROM ZUTEI Z WHERE Z.ZUMANDANT = A.GLMANDANT AND ZULO=GLLO AND ZULB=GLLB AND Z.ZUPARTIE = A.GLPARTIE AND Z.ZUPARTIEKZ = A.GLPARTIEKZ AND Z.ZULBEHI = A.GLLBEHI) AS ALLOKG,LPLBKZ  FROM GLLG A,GPDAT B,LAGPL,GPBST WHERE  A.GLLO=LPLO AND A.GLLB=LPLB AND A.GLLP=LPPLATZ AND A.GLMANDANT = B.GPMANDANT AND A.GLPARTIE = B.GPPARTIE AND GPBMANDANT=GPMANDANT AND GPBPARTIE=GPPARTIE AND GPBPARTIEKZ=GLPARTIEKZ  AND A.GLPARTIEKZ = B.GPPARTIEKZ"
                        + " AND GLLO=GPBLO AND (GLLB=GPBLB OR GPBLB='Y*') AND GPGART=:GAART AND GPGVAR=:GAVAR AND GPGFARBE= :GAFARBE AND GPSPPART=:GPSPPART AND GPFBPART=:GPFBPART AND GLLO=:GLLO AND GLLB=:GLLB AND GLAUFMSL=:GLAUFMSL  ) X WHERE X.KG-NVL(X.ALLOKG,0)>0 "
                        + " ORDER BY KG,FREEKG,ALLOKG";
                    or1.Parameters.Add("GAART", OracleType.NVarChar).Value = dwData["GAART"].ToString();
                    or1.Parameters.Add("GAVAR", OracleType.NVarChar).Value = dwData["GAVAR"].ToString();
                    or1.Parameters.Add("GAFARBE", OracleType.NVarChar).Value = dwData["GAFARBE"].ToString();
                    or1.Parameters.Add("GPSPPART", OracleType.NVarChar).Value = dwData["GPSPPART"].ToString();
                    or1.Parameters.Add("GPFBPART", OracleType.NVarChar).Value = dwData["GPFBPART"].ToString();
                    or1.Parameters.Add("GLAUFMSL", OracleType.NVarChar).Value = dwData["GLAUFMSL"].ToString();
                    or1.Parameters.Add("GLLO", OracleType.NVarChar).Value = dwData["GLLO"].ToString();
                    or1.Parameters.Add("GLLB", OracleType.NVarChar).Value = dwData["GLLB"].ToString();
                    break;
                case 4:
                    or1.CommandText = "SELECT M.GLMANDANT,GLPARTIE,GPGART,GPGVAR,GPGFARBE,GLPARTIEKZ,GLLO,GLLB,GLLP,GLLBEH,GLLBEHI,GLBSTNET,GLBSTKG,GLBSTKA,GLBSTSP,GLBSTNET,GLBEM,GLUBDAT, GPSPPART,GPFBPART,GPZWPART,GLPRODDAT,GLKOND,NVL(ZUAUFKRS||ZUAUFLFD||ZUAUFPOS,' ') AS JP04,CASE WHEN ZUMANDANT='00' THEN 'PV' ELSE ' ' END AS JP05 FROM GLLG M JOIN GPDAT ON GLMANDANT=GPMANDANT AND GLPARTIE=GPPARTIE "
                        + " LEFT JOIN ZUTEI ON ZULBEHI=GLLBEHI"
                        + " WHERE GLLO=:GLLO AND GLLB=:GLLB AND GLLP=:GLLP "
                        //+" AND GLPRODDAT>=:NEWDATE AND GLPRODDAT<=:CHGDATE "
                        + " AND GPSPPART LIKE '" + dwData["GPSPPART"].ToString().Trim() + "%' "
                        + " ORDER BY GLLBEHI";
                    or1.Parameters.Add("GLLO", OracleType.NVarChar).Value = dwData["GLLO"].ToString();
                    or1.Parameters.Add("GLLB", OracleType.NVarChar).Value = dwData["GLLB"].ToString();
                    or1.Parameters.Add("GLLP", OracleType.NVarChar).Value = dwData["GLLP"].ToString();
                    //or1.Parameters.Add("NEWDATE", OracleType.NVarChar).Value = dwData["NEWDATE"].ToString();
                    //or1.Parameters.Add("CHGDATE", OracleType.NVarChar).Value = dwData["CHGDATE"].ToString();
                    break;
                case 5:
                    or1.CommandText = "SELECT LPLBKZ,GPSPPART,GPFBPART,GPBPARTIE,GPBBSTKGZ,GPBPARTIEKZ,X.GLBEM,X.GLLBEHI,X.GPGART,X.GLLO,X.GLLB,X.GLLP,X.GPBLO,X.GPBLB,X.GPGVAR,X.GPGFARBE,X.KG,NVL(X.ALLOKG,0) AS ALLOKG,X.KG-NVL(X.ALLOKG,0) AS FREEKG FROM (SELECT GPSPPART,GPFBPART, GPBPARTIE,GPBPARTIEKZ,GPBBSTKGZ ,A.GLLBEHI,A.GLLO,A.GLLB,A.GLBEM,A.GLLP,GPBLO,GPBLB,B.GPGART,B.GPGVAR,B.GPGFARBE,A.GLBSTNET*0.001 AS KG, (SELECT SUM(DECODE(Z.ZUERLKZ,'J',0,Z.ZUEINTKG-Z.ZUENTNKG)*0.001) FROM ZUTEI Z WHERE Z.ZUMANDANT = A.GLMANDANT AND ZULO=GLLO AND ZULB=GLLB AND Z.ZUPARTIE = A.GLPARTIE AND Z.ZUPARTIEKZ = A.GLPARTIEKZ AND Z.ZULBEHI = A.GLLBEHI) AS ALLOKG,LPLBKZ  FROM GLLG A,GPDAT B,LAGPL,GPBST WHERE  A.GLLO=LPLO AND A.GLLB=LPLB AND A.GLLP=LPPLATZ AND A.GLMANDANT = B.GPMANDANT AND A.GLPARTIE = B.GPPARTIE AND GPBMANDANT=GPMANDANT AND GPBPARTIE=GPPARTIE AND GPBPARTIEKZ=GLPARTIEKZ  AND A.GLPARTIEKZ = B.GPPARTIEKZ  AND GLLO=GPBLO AND   GLLB=GPBLB"
                        + " AND GPGART=:GAART AND GPGVAR=:GAVAR AND GPGFARBE= :GAFARBE AND GPSPPART=:GPSPPART AND GPFBPART=:GPFBPART AND GLLO=:GLLO AND GLLB=:GLLB AND GLAUFMSL=:GLAUFMSL AND RTRIM(GLLO||GLLB||GLLP)  LIKE '"+dwData["ADDT"].ToString()+"%'  ) X WHERE X.KG-NVL(X.ALLOKG,0)>0 "
                        + " ORDER BY KG,FREEKG,ALLOKG";
                    or1.Parameters.Add("GAART", OracleType.NVarChar).Value = dwData["GAART"].ToString();
                    or1.Parameters.Add("GAVAR", OracleType.NVarChar).Value = dwData["GAVAR"].ToString();
                    or1.Parameters.Add("GAFARBE", OracleType.NVarChar).Value = dwData["GAFARBE"].ToString();
                    or1.Parameters.Add("GPSPPART", OracleType.NVarChar).Value = dwData["GPSPPART"].ToString();
                    or1.Parameters.Add("GPFBPART", OracleType.NVarChar).Value = dwData["GPFBPART"].ToString();
                    or1.Parameters.Add("GLAUFMSL", OracleType.NVarChar).Value = dwData["GLAUFMSL"].ToString();
                    or1.Parameters.Add("GLLO", OracleType.NVarChar).Value = dwData["GLLO"].ToString();
                    or1.Parameters.Add("GLLB", OracleType.NVarChar).Value = dwData["GLLB"].ToString();
                  
                    break;

                case 6:
                    or1.CommandText = "SELECT M.GLMANDANT,GLPARTIE,GPGART,GPGVAR,GPGFARBE,GLPARTIEKZ,GLLO,GLLB,GLLP, "
                        + " GLLBEH,GLLBEHI,GLBSTNET,GLBSTKG,GLBSTKA,GLBSTSP,GLBEM,GLUBDAT,  "
                        + " GPSPPART,GPFBPART,GPZWPART,GLPRODDAT,GLKOND,NVL(ZUAUFKRS||ZUAUFLFD||ZUAUFPOS,' ') AS JP04, "
                        + " nvl((CASE WHEN ZUMANDANT='00' THEN 'PV' ELSE ' ' END),' ') AS JP05  "
                        + " FROM GLLG M JOIN GPDAT ON GLMANDANT=GPMANDANT AND GLPARTIE=GPPARTIE "
                        + " LEFT JOIN ZUTEI ON ZULBEHI=GLLBEHI "
                        + " WHERE GLLO=:GLLO AND GLLB=:GLLB AND GLLP=:GLLP AND GLPRODDAT>=:NEWDATE "
                        + " AND GLPRODDAT<=:CHGDATE AND GPSPPART LIKE '" + dwData["GPSPPART"].ToString().Trim() + "%'  "
                        + " UNION ALL "
                        + " SELECT M.GLMANDANT,GLPARTIE,GPGART,GPGVAR,GPGFARBE,GLPARTIEKZ,GLLO,GLLB,GLLP, "
                        + " '9999999999' AS GLLBEH,'9999999999' AS GLLBEHI,SUM(GLBSTNET) AS GLBSTNET,SUM(GLBSTKG) AS GLBSTKG, "
                        + " SUM(GLBSTKA) AS GLBSTKA,SUM(GLBSTSP) AS GLBSTSP, "
                        + " 'total:' as GLBEM,0 AS GLUBDAT,  "
                        + " GPSPPART,GPFBPART,GPZWPART,0 AS GLPRODDAT,0 AS GLKOND,' ' AS JP04, "
                        + " ' ' AS JP05  "
                        + " FROM GLLG M JOIN GPDAT ON GLMANDANT=GPMANDANT AND GLPARTIE=GPPARTIE "
                        + " WHERE GLLO=:GLLO AND GLLB=:GLLB AND GLLP=:GLLP AND GLPRODDAT>=:NEWDATE "
                        + " AND GLPRODDAT<=:CHGDATE AND GPSPPART LIKE '" + dwData["GPSPPART"].ToString().Trim() + "%' "
                        + " GROUP BY M.GLMANDANT,GLPARTIE,GPGART,GPGVAR,GPGFARBE,GLPARTIEKZ,GLLO,GLLB,GLLP, "
                        + " GPSPPART,GPFBPART,GPZWPART "
                        + " ORDER BY GPSPPART,GLLBEHI ";
                    or1.Parameters.Add("GLLO", OracleType.NVarChar).Value = dwData["GLLO"].ToString();
                    or1.Parameters.Add("GLLB", OracleType.NVarChar).Value = dwData["GLLB"].ToString();
                    or1.Parameters.Add("GLLP", OracleType.NVarChar).Value = dwData["GLLP"].ToString();
                    or1.Parameters.Add("NEWDATE", OracleType.NVarChar).Value = dwData["NEWDATE"].ToString();
                    or1.Parameters.Add("CHGDATE", OracleType.NVarChar).Value = dwData["CHGDATE"].ToString();
                    break;

                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "GLLG";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
        public void UpdateGLLG(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    break;
                case 2:
                    or1 = OracleDB.CreateCommand();
                    or1.CommandText = "UPDATE GLLG SET "
                        + "GLBSTKG=:GLBSTKG," 
                        + "GLBSTNET=:GLBSTNET,GLBEM = :GLBEM "                        
                        + " WHERE GLLBEHI=:GLLBEHI"; 
                    or1.Parameters.Add("GLLBEHI", OracleType.NVarChar).Value = dwData["GLLBEHI"].ToString();
                    or1.Parameters.Add("GLBSTNET", OracleType.NVarChar).Value = dwData["GLBSTNET"].ToString();
                    or1.Parameters.Add("GLBSTKG", OracleType.NVarChar).Value = dwData["GLBSTKG"].ToString();
                    or1.Parameters.Add("GLBEM", OracleType.NVarChar).Value = dwData["JP10"].ToString();
                    break;
                case 3:
                    or1 = OracleDB.CreateCommand();
                    or1.CommandText = "UPDATE GLLG SET "
                        
                        + "GLLO=:GLLO,"
                        + "GLLB=:GLLB,"
                        + "GLLP=:GLLP " 
                        + " WHERE GLLBEHI=:GLLBEHI";
                    or1.Parameters.Add("GLLO", OracleType.NVarChar).Value = dwData["CPLO"].ToString();
                    or1.Parameters.Add("GLLB", OracleType.NVarChar).Value = dwData["CPLB"].ToString();
                    or1.Parameters.Add("GLLP", OracleType.NVarChar).Value = dwData["CPLP"].ToString();
                    or1.Parameters.Add("GLLBEHI", OracleType.NVarChar).Value = dwData["GLLBEHI"].ToString();
                  
                    break;

            }
            Connect2Proxy(or1);
        }

    }
}
